﻿using Microsoft.Practices.EnterpriseLibrary.Data;
using SH3H.CSM.Common.LogUtil;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DataAccess
{
    public class OperateDataAccess
    {
        private static DatabaseProviderFactory factory=null;
        static OperateDataAccess() {
            factory = new DatabaseProviderFactory();
        }
        public int GetData(int id)
        {
            Database db = factory.Create("DefaultConnection");
            string sqlText = @"SELECT VALUE FROM TestOne WHERE ID=@ID";
            //DbCommand cmd = db.GetSqlStringCommand(sql);
            try
            {
                using (var cmd = db.GetSqlStringCommand(sqlText))
                {
                    db.AddInParameter(cmd, "@ID", DbType.Int32, id);
                    cmd.CommandText = sqlText.ToString();
                    DataTable dt = db.ExecuteDataSet(cmd).Tables[0];
                    if (dt.Rows.Count == 0) {
                        return -100;
                    }
                    return Convert.ToInt32(dt.Rows[0][0]);
                }
            }
            catch (Exception e) {
                LogUtil.Error(e.Message,e);
                return -1;
            }
        }

        public bool UpdateTimes(int id) {
            Database db = factory.Create("conn_csm");
            string sqlText = @"UPDATE TestOne SET VALUE2=VALUE2+1 WHERE ID=@ID";
            //DbCommand cmd = db.GetSqlStringCommand(sql);
            try
            {
                using (var cmd = db.GetSqlStringCommand(sqlText))
                {
                    db.AddInParameter(cmd, "@ID", DbType.Int32, id);
                    cmd.CommandText = sqlText.ToString();
                    return db.ExecuteNonQuery(cmd)>0;
                }
            }
            catch (Exception e)
            {
                LogUtil.Error(e.Message, e);
                return false;
            }
        }

        public bool sqlBulkCopy() {
            DataTable dt = new DataTable();
            dt.Columns.Add("ID", typeof(System.Int32));
            dt.Columns.Add("VALUE", typeof(System.String));
            for (int i = 1; i < 5; i++) {
                DataRow dr = dt.NewRow();
                dr["ID"] = i;
                dr["VALUE"] = i.ToString();
                dt.Rows.Add(dr);
            }
            try
            {
                Database db = factory.Create("conn_csm");
                using (var conn = db.CreateConnection())
                {
                    conn.Open();
                    SqlBulkCopy bulk = new SqlBulkCopy((SqlConnection)conn);
                    //目的地的数据表名
                    bulk.DestinationTableName = "dbo.TestTwo";
                    //数据源与目的数据表的映射关系
                    foreach (DataColumn c in dt.Columns)
                    {
                        bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(c.ColumnName, c.ColumnName));
                    }
                    bulk.BatchSize = dt.Rows.Count;
                    bulk.WriteToServer(dt);
                    conn.Dispose();
                }

                return true;
            }
            catch (Exception e) {
                LogUtil.Error(e.Message, e);
                return false;
            }
        }

        public int Pro(int id) {
            Database db = factory.Create("conn_csm");
            int err=-99;
            try
            {
                using (var cmd = db.GetStoredProcCommand("Pro_RollTran"))
                {
                    db.AddInParameter(cmd, "@ID", DbType.Int32, id);
                    db.AddOutParameter(cmd, "@errid", DbType.Int32,999);
                    db.ExecuteNonQuery(cmd);
                    err = int.Parse(db.GetParameterValue(cmd, "@errid").ToString());
                    return err;
                }
            }
            catch (Exception e)
            {
                LogUtil.Error(e.Message, e);
                return err;
            }
        }

        public string getData()
        {

            Database db = factory.Create("mysqlconn");
            string sqlText = @"SELECT NAME FROM user_csm WHERE ID=?ID";
            try
            {
                using (var cmd = db.GetSqlStringCommand(sqlText))
                {
                    db.AddInParameter(cmd, "?ID", DbType.Int32, 1);
                    cmd.CommandText = sqlText.ToString();
                    DataTable dt = db.ExecuteDataSet(cmd).Tables[0];
                    if (dt.Rows.Count == 0)
                    {
                        return "zero";
                    }
                    return dt.Rows[0][0].ToString();
                }
            }
            catch (Exception e)
            {
                return "catch";
            }

        }
    }
}
